package Database;

import java.sql.*;

public class customer 
{
	private Connection conn = null;
	private PreparedStatement prepSt = null;
	private dbConnection db;
	private ResultSet rSet;
	
	public customer()
	{
		db = new dbConnection();
		conn = db.openDB();
	}
	
	public void createCustomerTable()
	{
		try
		{
			String create = "CREATE TABLE Customer " + "(customerID NUMBER PRIMARY KEY, name VARCHAR(30), " +
					"address VARCHAR2(30), phone VARCHAR2(15), email VARCHAR2(30))";
			prepSt = conn.prepareStatement(create);
			prepSt.executeUpdate();
			
			String insert = "INSERT INTO Customer(customerID,name,address,phone,email) values(?,?,?,?,?)";
			prepSt = conn.prepareStatement(insert);
			
			prepSt.setInt(1, 001);
			prepSt.setString(2, "Marlan");
			prepSt.setString(3, "Marlan's Street");
			prepSt.setString(4, "0866664350");
			prepSt.setString(5, "marleys@marlan.com");
			prepSt.executeUpdate();
			
			prepSt.setInt(1, 002);
			prepSt.setString(2, "Lee");
			prepSt.setString(3, "Lee's Street");
			prepSt.setString(4, "0875146152");
			prepSt.setString(5, "lee@lee.com");
			prepSt.executeUpdate();
			
			prepSt.setInt(1, 003);
			prepSt.setString(2, "David");
			prepSt.setString(3, "David's Street");
			prepSt.setString(4, "0851349626");
			prepSt.setString(5, "david@david.com");
			prepSt.executeUpdate();
			
			conn.commit();
			prepSt.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public void dropCustomerTable()
	{
		try
		{
			String drop = "DROP TABLE Customer";
			prepSt = conn.prepareStatement(drop);
			prepSt.executeUpdate();
			System.out.println();
			System.out.println("Table dropped");
			conn.commit();
			prepSt.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public void queryCustomer()
	{
		try
		{
			String query = "SELECT * FROM Customer ORDER BY customerID";
			prepSt = conn.prepareStatement(query);
			rSet = prepSt.executeQuery();
			System.out.println();
			System.out.println("ID\tName\t\tAddress \t\tPhone\t\tEmail");
			while (rSet.next())
			{
				System.out.println(rSet.getInt(1)+ "\t" +rSet.getString(2)+ "\t\t"
						+rSet.getString(3)+ "\t\t" +rSet.getString(4)+ "\t" +rSet.getString(5));
			}
			prepSt.close();
			rSet.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public static void main(String[] args) 
	{
		customer thisQ = new customer();
		thisQ.createCustomerTable();
		thisQ.queryCustomer();
		thisQ.dropCustomerTable();
		thisQ.db.closeDB();
	}
}
